﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using DevExpress.XtraReports.UI;
using DevExpress.XtraCharts;
using ERP_RORZE_SYSTEM;
using System.Data;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class rpt_F4LocalTrouble : DevExpress.XtraReports.UI.XtraReport
    {
        public string year;
        public string plan;
        private DataTable TotalTable;
        private DataTable ShowTable;
        
        private DataTable RatioTable;

        private DataTable MistakeTable;
        private DataTable MistakeTableShow;
        private DataTable MistakeChartTable;
        private DataTable MistakeRateChartTable;

        private DataTable ReasonTable;
        private DataTable ReasonTableShow;
        private DataTable ReasonChartTable;
        private DataTable ReasonRateChartTable;

        private DataTable NGTypeTable;
        private DataTable NGTypeTableShow;        
        private DataTable NGTypeRateChartTable;

        private DataTable TargetTable;
        private DataTable TargetTableShow;
        private DataTable TargetChartTable;

        public rpt_F4LocalTrouble()
        {
            InitializeComponent();
            this.year = null;
            this.plan = null;
            this.TotalTable = new DataTable();
            this.ShowTable = new DataTable();
            
            this.RatioTable = new DataTable();
            this.RatioTable.Columns.Add("Month", typeof(string));
            this.RatioTable.Columns.Add("Ratio", typeof(double));

            this.MistakeTable = new DataTable();
            this.MistakeTableShow = new DataTable();
            this.MistakeChartTable = new DataTable();
            this.MistakeRateChartTable = new DataTable();
            this.MistakeRateChartTable.Columns.Add("Items", typeof(string));
            this.MistakeRateChartTable.Columns.Add("Value", typeof(double));

            this.ReasonTable = new DataTable();
            this.ReasonTableShow = new DataTable();
            this.ReasonChartTable = new DataTable();
            this.ReasonRateChartTable = new DataTable();
            this.ReasonRateChartTable.Columns.Add("Items", typeof(string));
            this.ReasonRateChartTable.Columns.Add("Value", typeof(double));

            this.NGTypeTable = new DataTable();
            this.NGTypeTableShow = new DataTable();
            this.NGTypeRateChartTable = new DataTable();
            this.NGTypeRateChartTable.Columns.Add("Items", typeof(string));
            this.NGTypeRateChartTable.Columns.Add("Value", typeof(double));

            this.TargetTable = new DataTable();
            this.TargetTableShow = new DataTable();
            this.TargetChartTable = new DataTable();
        }

        private void BindData()
        {
            /// Target
            this.TargetReport.DataSource = this.TargetTableShow;

            targetJanLabel.Text = "Jan-" + this.year.Substring(2, 2);
            targetFebLabel.Text = "Feb-" + this.year.Substring(2, 2);
            targetMarLabel.Text = "Mar-" + this.year.Substring(2, 2);
            targetAprLabel.Text = "Apr-" + this.year.Substring(2, 2);
            targetMayLabel.Text = "May-" + this.year.Substring(2, 2);
            targetJunLabel.Text = "Jun-" + this.year.Substring(2, 2);
            targetJulLabel.Text = "Jul-" + this.year.Substring(2, 2);
            targetAugLabel.Text = "Aug-" + this.year.Substring(2, 2);
            targetSepLabel.Text = "Sep-" + this.year.Substring(2, 2);
            targetOctLabel.Text = "Oct-" + this.year.Substring(2, 2);
            targetNovLabel.Text = "Nov-" + this.year.Substring(2, 2);
            targetDecLabel.Text = "Dec-" + this.year.Substring(2, 2);

            itemName.DataBindings.Add("Text", this.MistakeType.DataSource, "Items");
            targetJan.DataBindings.Add("Text", this.MistakeType.DataSource, "Jan-" + this.year.Substring(2, 2));
            targetFeb.DataBindings.Add("Text", this.MistakeType.DataSource, "Feb-" + this.year.Substring(2, 2));
            targetMar.DataBindings.Add("Text", this.MistakeType.DataSource, "Mar-" + this.year.Substring(2, 2));
            targetApr.DataBindings.Add("Text", this.MistakeType.DataSource, "Apr-" + this.year.Substring(2, 2));
            targetMay.DataBindings.Add("Text", this.MistakeType.DataSource, "May-" + this.year.Substring(2, 2));
            targetJun.DataBindings.Add("Text", this.MistakeType.DataSource, "Jun-" + this.year.Substring(2, 2));
            targetJul.DataBindings.Add("Text", this.MistakeType.DataSource, "Jul-" + this.year.Substring(2, 2));
            targetAug.DataBindings.Add("Text", this.MistakeType.DataSource, "Aug-" + this.year.Substring(2, 2));
            targetSep.DataBindings.Add("Text", this.MistakeType.DataSource, "Sep-" + this.year.Substring(2, 2));
            targetOct.DataBindings.Add("Text", this.MistakeType.DataSource, "Oct-" + this.year.Substring(2, 2));
            targetNov.DataBindings.Add("Text", this.MistakeType.DataSource, "Nov-" + this.year.Substring(2, 2));
            targetDec.DataBindings.Add("Text", this.MistakeType.DataSource, "Dec-" + this.year.Substring(2, 2));
            targetTotal.DataBindings.Add("Text", this.MistakeType.DataSource, this.year);

            this.targetChart.Series["PLAN"].DataSource = this.TargetChartTable;
            this.targetChart.Series["PLAN"].ArgumentScaleType = ScaleType.Auto;
            this.targetChart.Series["PLAN"].ArgumentDataMember = "Month";
            this.targetChart.Series["PLAN"].ValueScaleType = ScaleType.Numerical;
            this.targetChart.Series["PLAN"].ValueDataMembers.AddRange(new string[] { "Plan" });

            this.targetChart.Series["ACTUAL"].DataSource = this.TargetChartTable;
            this.targetChart.Series["ACTUAL"].ArgumentScaleType = ScaleType.Auto;
            this.targetChart.Series["ACTUAL"].ArgumentDataMember = "Month";
            this.targetChart.Series["ACTUAL"].ValueScaleType = ScaleType.Numerical;
            this.targetChart.Series["ACTUAL"].ValueDataMembers.AddRange(new string[] { "Actual" });

            this.targetChart.Series["NG (PC)"].DataSource = this.TargetChartTable;
            this.targetChart.Series["NG (PC)"].ArgumentScaleType = ScaleType.Auto;
            this.targetChart.Series["NG (PC)"].ArgumentDataMember = "Month";
            this.targetChart.Series["NG (PC)"].ValueScaleType = ScaleType.Numerical;
            this.targetChart.Series["NG (PC)"].ValueDataMembers.AddRange(new string[] { "NGQTy" });

            this.targetChart.Series["Q'TY COMP"].DataSource = this.TargetChartTable;
            this.targetChart.Series["Q'TY COMP"].ArgumentScaleType = ScaleType.Auto;
            this.targetChart.Series["Q'TY COMP"].ArgumentDataMember = "Month";
            this.targetChart.Series["Q'TY COMP"].ValueScaleType = ScaleType.Numerical;
            this.targetChart.Series["Q'TY COMP"].ValueDataMembers.AddRange(new string[] { "TotalQTy" });

            // Total Report
            this.Total.DataSource = this.ShowTable;

            yearLabel.Text = this.year;
            janLabel.Text = "Jan-" + this.year.Substring(2, 2);
            febLabel.Text = "Feb-" + this.year.Substring(2, 2);
            marLabel.Text = "Mar-" + this.year.Substring(2, 2);
            aprLabel.Text = "Apr-" + this.year.Substring(2, 2);
            mayLabel.Text = "May-" + this.year.Substring(2, 2);
            junLabel.Text = "Jun-" + this.year.Substring(2, 2);
            julLabel.Text = "Jul-" + this.year.Substring(2, 2);
            augLabel.Text = "Aug-" + this.year.Substring(2, 2);
            sepLabel.Text = "Sep-" + this.year.Substring(2, 2);
            octLabel.Text = "Oct-" + this.year.Substring(2, 2);
            novLabel.Text = "Nov-" + this.year.Substring(2, 2);
            decLabel.Text = "Dec-" + this.year.Substring(2, 2);
            
            itemDetail.DataBindings.Add("Text", this.Total.DataSource, "Items");
            yearWork.DataBindings.Add("Text", this.Total.DataSource, this.year);
            janWork.DataBindings.Add("Text", this.Total.DataSource, "Jan-" + this.year.Substring(2, 2));
            febWork.DataBindings.Add("Text", this.Total.DataSource, "Feb-" + this.year.Substring(2, 2));
            marWork.DataBindings.Add("Text", this.Total.DataSource, "Mar-" + this.year.Substring(2, 2));
            aprWork.DataBindings.Add("Text", this.Total.DataSource, "Apr-" + this.year.Substring(2, 2));
            mayWork.DataBindings.Add("Text", this.Total.DataSource, "May-" + this.year.Substring(2, 2));
            junWork.DataBindings.Add("Text", this.Total.DataSource, "Jun-" + this.year.Substring(2, 2));
            julWork.DataBindings.Add("Text", this.Total.DataSource, "Jul-" + this.year.Substring(2, 2));
            augWork.DataBindings.Add("Text", this.Total.DataSource, "Aug-" + this.year.Substring(2, 2));
            sepWork.DataBindings.Add("Text", this.Total.DataSource, "Sep-" + this.year.Substring(2, 2));
            octWork.DataBindings.Add("Text", this.Total.DataSource, "Oct-" + this.year.Substring(2, 2));
            novWork.DataBindings.Add("Text", this.Total.DataSource, "Nov-" + this.year.Substring(2, 2));
            decWork.DataBindings.Add("Text", this.Total.DataSource, "Dec-" + this.year.Substring(2, 2));
                        

            // Specify data members to bind the series.
            ratioChart.Series[0].DataSource = RatioTable;
            ratioChart.Series[0].ArgumentScaleType = ScaleType.Auto;
            ratioChart.Series[0].ArgumentDataMember = "Month";
            ratioChart.Series[0].ValueScaleType = ScaleType.Numerical;
            ratioChart.Series[0].ValueDataMembers.AddRange(new string[] { "Ratio" });

            // Mistake
            this.Mistake.DataSource = this.MistakeTableShow;

            mistakeJanLabel.Text = "Jan-" + this.year.Substring(2, 2);
            mistakeFebLabel.Text = "Feb-" + this.year.Substring(2, 2);
            mistakeMarLabel.Text = "Mar-" + this.year.Substring(2, 2);
            mistakeAprLabel.Text = "Apr-" + this.year.Substring(2, 2);
            mistakeMayLabel.Text = "May-" + this.year.Substring(2, 2);
            mistakeJunLabel.Text = "Jun-" + this.year.Substring(2, 2);
            mistakeJulLabel.Text = "Jul-" + this.year.Substring(2, 2);
            mistakeAugLabel.Text = "Aug-" + this.year.Substring(2, 2);
            mistakeSepLabel.Text = "Sep-" + this.year.Substring(2, 2);
            mistakeOctLabel.Text = "Oct-" + this.year.Substring(2, 2);
            mistakeNovLabel.Text = "Nov-" + this.year.Substring(2, 2);
            mistakeDecLabel.Text = "Dec-" + this.year.Substring(2, 2);

            mistakeName.DataBindings.Add("Text", this.Mistake.DataSource, "MistakeName");
            mistakeCode.DataBindings.Add("Text", this.Mistake.DataSource, "MistakeCode");
            mistakeJan.DataBindings.Add("Text", this.Mistake.DataSource, "Jan-" + this.year.Substring(2, 2));
            mistakeFeb.DataBindings.Add("Text", this.Mistake.DataSource, "Feb-" + this.year.Substring(2, 2));
            mistakeMar.DataBindings.Add("Text", this.Mistake.DataSource, "Mar-" + this.year.Substring(2, 2));
            mistakeApr.DataBindings.Add("Text", this.Mistake.DataSource, "Apr-" + this.year.Substring(2, 2));
            mistakeMay.DataBindings.Add("Text", this.Mistake.DataSource, "May-" + this.year.Substring(2, 2));
            mistakeJun.DataBindings.Add("Text", this.Mistake.DataSource, "Jun-" + this.year.Substring(2, 2));
            mistakeJul.DataBindings.Add("Text", this.Mistake.DataSource, "Jul-" + this.year.Substring(2, 2));
            mistakeAug.DataBindings.Add("Text", this.Mistake.DataSource, "Aug-" + this.year.Substring(2, 2));
            mistakeSep.DataBindings.Add("Text", this.Mistake.DataSource, "Sep-" + this.year.Substring(2, 2));
            mistakeOct.DataBindings.Add("Text", this.Mistake.DataSource, "Oct-" + this.year.Substring(2, 2));
            mistakeNov.DataBindings.Add("Text", this.Mistake.DataSource, "Nov-" + this.year.Substring(2, 2));
            mistakeDec.DataBindings.Add("Text", this.Mistake.DataSource, "Dec-" + this.year.Substring(2, 2));
            mistakeTotal.DataBindings.Add("Text", this.Mistake.DataSource, this.year);
            mistakePercent.DataBindings.Add("Text", this.Mistake.DataSource, "%");            

            for (int i = 1; i < this.MistakeChartTable.Columns.Count; i++)
            {
                this.mistakeChart.Series.Add(this.MistakeChartTable.Columns[i].ColumnName, ViewType.Bar);
                // Specify data members to bind the series.
                this.mistakeChart.Series[this.MistakeChartTable.Columns[i].ColumnName].DataSource = this.MistakeChartTable;
                this.mistakeChart.Series[this.MistakeChartTable.Columns[i].ColumnName].ArgumentScaleType = ScaleType.Auto;
                this.mistakeChart.Series[this.MistakeChartTable.Columns[i].ColumnName].ArgumentDataMember = "Month";
                this.mistakeChart.Series[this.MistakeChartTable.Columns[i].ColumnName].ValueScaleType = ScaleType.Numerical;
                this.mistakeChart.Series[this.MistakeChartTable.Columns[i].ColumnName].ValueDataMembers.AddRange(new string[] { this.MistakeChartTable.Columns[i].ColumnName });
                this.mistakeChart.Series[this.MistakeChartTable.Columns[i].ColumnName].LabelsVisibility = DevExpress.Utils.DefaultBoolean.False;
            }
            
            this.mistakeRateChart.Series["MistakeRate"].DataSource = this.MistakeRateChartTable;
            this.mistakeRateChart.Series["MistakeRate"].ArgumentScaleType = ScaleType.Auto;
            this.mistakeRateChart.Series["MistakeRate"].ArgumentDataMember = "Items";
            this.mistakeRateChart.Series["MistakeRate"].ValueScaleType = ScaleType.Numerical;
            this.mistakeRateChart.Series["MistakeRate"].ValueDataMembers.AddRange(new string[] { "Value" });
            this.mistakeRateChart.Series["MistakeRate"].LegendText = this.mistakeRateChart.Series["MistakeRate"].ArgumentDataMember;
            this.mistakeRateChart.Series["MistakeRate"].LegendPointOptions.PointView = PointView.Argument; 

            /// Reason
            this.MistakeReason.DataSource = this.ReasonTableShow;

            reasonJanLabel.Text = "Jan-" + this.year.Substring(2, 2);
            reasonFebLabel.Text = "Feb-" + this.year.Substring(2, 2);
            reasonMarLabel.Text = "Mar-" + this.year.Substring(2, 2);
            reasonAprLabel.Text = "Apr-" + this.year.Substring(2, 2);
            reasonMayLabel.Text = "May-" + this.year.Substring(2, 2);
            reasonJunLabel.Text = "Jun-" + this.year.Substring(2, 2);
            reasonJulLabel.Text = "Jul-" + this.year.Substring(2, 2);
            reasonAugLabel.Text = "Aug-" + this.year.Substring(2, 2);
            reasonSepLabel.Text = "Sep-" + this.year.Substring(2, 2);
            reasonOctLabel.Text = "Oct-" + this.year.Substring(2, 2);
            reasonNovLabel.Text = "Nov-" + this.year.Substring(2, 2);
            reasonDecLabel.Text = "Dec-" + this.year.Substring(2, 2);

            reasonName.DataBindings.Add("Text", this.MistakeReason.DataSource, "ReasonName");
            reasonCode.DataBindings.Add("Text", this.MistakeReason.DataSource, "ReasonCode");
            reasonJan.DataBindings.Add("Text", this.MistakeReason.DataSource, "Jan-" + this.year.Substring(2, 2));
            reasonFeb.DataBindings.Add("Text", this.MistakeReason.DataSource, "Feb-" + this.year.Substring(2, 2));
            reasonMar.DataBindings.Add("Text", this.MistakeReason.DataSource, "Mar-" + this.year.Substring(2, 2));
            reasonApr.DataBindings.Add("Text", this.MistakeReason.DataSource, "Apr-" + this.year.Substring(2, 2));
            reasonMay.DataBindings.Add("Text", this.MistakeReason.DataSource, "May-" + this.year.Substring(2, 2));
            reasonJun.DataBindings.Add("Text", this.MistakeReason.DataSource, "Jun-" + this.year.Substring(2, 2));
            reasonJul.DataBindings.Add("Text", this.MistakeReason.DataSource, "Jul-" + this.year.Substring(2, 2));
            reasonAug.DataBindings.Add("Text", this.MistakeReason.DataSource, "Aug-" + this.year.Substring(2, 2));
            reasonSep.DataBindings.Add("Text", this.MistakeReason.DataSource, "Sep-" + this.year.Substring(2, 2));
            reasonOct.DataBindings.Add("Text", this.MistakeReason.DataSource, "Oct-" + this.year.Substring(2, 2));
            reasonNov.DataBindings.Add("Text", this.MistakeReason.DataSource, "Nov-" + this.year.Substring(2, 2));
            reasonDec.DataBindings.Add("Text", this.MistakeReason.DataSource, "Dec-" + this.year.Substring(2, 2));
            reasonTotal.DataBindings.Add("Text", this.MistakeReason.DataSource, this.year);
            reasonPercent.DataBindings.Add("Text", this.MistakeReason.DataSource, "%");

            for (int i = 1; i < this.ReasonChartTable.Columns.Count; i++)
            {
                this.reasonChart.Series.Add(this.ReasonChartTable.Columns[i].ColumnName, ViewType.Bar);
                // Specify data members to bind the series.
                this.reasonChart.Series[this.ReasonChartTable.Columns[i].ColumnName].DataSource = this.ReasonChartTable;
                this.reasonChart.Series[this.ReasonChartTable.Columns[i].ColumnName].ArgumentScaleType = ScaleType.Auto;
                this.reasonChart.Series[this.ReasonChartTable.Columns[i].ColumnName].ArgumentDataMember = "Month";
                this.reasonChart.Series[this.ReasonChartTable.Columns[i].ColumnName].ValueScaleType = ScaleType.Numerical;
                this.reasonChart.Series[this.ReasonChartTable.Columns[i].ColumnName].ValueDataMembers.AddRange(new string[] { this.ReasonChartTable.Columns[i].ColumnName });
                this.reasonChart.Series[this.ReasonChartTable.Columns[i].ColumnName].LabelsVisibility = DevExpress.Utils.DefaultBoolean.False;
            }

            this.reasonRateChart.Series["ReasonRate"].DataSource = this.ReasonRateChartTable;
            this.reasonRateChart.Series["ReasonRate"].ArgumentScaleType = ScaleType.Auto;
            this.reasonRateChart.Series["ReasonRate"].ArgumentDataMember = "Items";
            this.reasonRateChart.Series["ReasonRate"].ValueScaleType = ScaleType.Numerical;
            this.reasonRateChart.Series["ReasonRate"].ValueDataMembers.AddRange(new string[] { "Value" });
            this.reasonRateChart.Series["ReasonRate"].LegendText = this.reasonRateChart.Series["ReasonRate"].ArgumentDataMember;
            this.reasonRateChart.Series["ReasonRate"].LegendPointOptions.PointView = PointView.Argument;

            /// NG Type
            this.MistakeType.DataSource = this.NGTypeTableShow;

            ngTypeJanLabel.Text = "Jan-" + this.year.Substring(2, 2);
            ngTypeFebLabel.Text = "Feb-" + this.year.Substring(2, 2);
            ngTypeMarLabel.Text = "Mar-" + this.year.Substring(2, 2);
            ngTypeAprLabel.Text = "Apr-" + this.year.Substring(2, 2);
            ngTypeMayLabel.Text = "May-" + this.year.Substring(2, 2);
            ngTypeJunLabel.Text = "Jun-" + this.year.Substring(2, 2);
            ngTypeJulLabel.Text = "Jul-" + this.year.Substring(2, 2);
            ngTypeAugLabel.Text = "Aug-" + this.year.Substring(2, 2);
            ngTypeSepLabel.Text = "Sep-" + this.year.Substring(2, 2);
            ngTypeOctLabel.Text = "Oct-" + this.year.Substring(2, 2);
            ngTypeNovLabel.Text = "Nov-" + this.year.Substring(2, 2);
            ngTypeDecLabel.Text = "Dec-" + this.year.Substring(2, 2);

            ngTypeName.DataBindings.Add("Text", this.MistakeType.DataSource, "TroubleKind");
            ngTypeJan.DataBindings.Add("Text", this.MistakeType.DataSource, "Jan-" + this.year.Substring(2, 2));
            ngTypeFeb.DataBindings.Add("Text", this.MistakeType.DataSource, "Feb-" + this.year.Substring(2, 2));
            ngTypeMar.DataBindings.Add("Text", this.MistakeType.DataSource, "Mar-" + this.year.Substring(2, 2));
            ngTypeApr.DataBindings.Add("Text", this.MistakeType.DataSource, "Apr-" + this.year.Substring(2, 2));
            ngTypeMay.DataBindings.Add("Text", this.MistakeType.DataSource, "May-" + this.year.Substring(2, 2));
            ngTypeJun.DataBindings.Add("Text", this.MistakeType.DataSource, "Jun-" + this.year.Substring(2, 2));
            ngTypeJul.DataBindings.Add("Text", this.MistakeType.DataSource, "Jul-" + this.year.Substring(2, 2));
            ngTypeAug.DataBindings.Add("Text", this.MistakeType.DataSource, "Aug-" + this.year.Substring(2, 2));
            ngTypeSep.DataBindings.Add("Text", this.MistakeType.DataSource, "Sep-" + this.year.Substring(2, 2));
            ngTypeOct.DataBindings.Add("Text", this.MistakeType.DataSource, "Oct-" + this.year.Substring(2, 2));
            ngTypeNov.DataBindings.Add("Text", this.MistakeType.DataSource, "Nov-" + this.year.Substring(2, 2));
            ngTypeDec.DataBindings.Add("Text", this.MistakeType.DataSource, "Dec-" + this.year.Substring(2, 2));
            ngTypeTotal.DataBindings.Add("Text", this.MistakeType.DataSource, this.year);
            ngTypePercent.DataBindings.Add("Text", this.MistakeType.DataSource, "%");
            
            this.ngtypeRateChart.Series["NGTypeRate"].DataSource = this.NGTypeRateChartTable;
            this.ngtypeRateChart.Series["NGTypeRate"].ArgumentScaleType = ScaleType.Auto;
            this.ngtypeRateChart.Series["NGTypeRate"].ArgumentDataMember = "Items";
            this.ngtypeRateChart.Series["NGTypeRate"].ValueScaleType = ScaleType.Numerical;
            this.ngtypeRateChart.Series["NGTypeRate"].ValueDataMembers.AddRange(new string[] { "Value" });
            this.ngtypeRateChart.Series["NGTypeRate"].LegendText = this.ngtypeRateChart.Series["NGTypeRate"].ArgumentDataMember;
            this.ngtypeRateChart.Series["NGTypeRate"].LegendPointOptions.PointView = PointView.Argument;
        }

        public void LoadData()
        {
            DataSet totalDataSet = new DataSet();
            DataSet mistakeDataSet = new DataSet();
            DataSet reasonDataSet = new DataSet();
            DataSet ngtypeDataSet = new DataSet();
            DataSet targetDataSet = new DataSet();

            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;

            // Get data for Total Local Trouble Report
            string sql = "SELECT 'Ratio (%)' AS [Items],"
                + " 0 AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [May-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Dec-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [" + this.year + "]"
                + " UNION ALL"
                + " SELECT 'NG Quantity (pcs)' AS [Items],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 1 THEN [NGQuantity] ELSE 0 END) AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 2 THEN [NGQuantity] ELSE 0 END) AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 3 THEN [NGQuantity] ELSE 0 END) AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 4 THEN [NGQuantity] ELSE 0 END) AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 5 THEN [NGQuantity] ELSE 0 END) AS [May-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 6 THEN [NGQuantity] ELSE 0 END) AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 7 THEN [NGQuantity] ELSE 0 END) AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 8 THEN [NGQuantity] ELSE 0 END) AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 9 THEN [NGQuantity] ELSE 0 END) AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 10 THEN [NGQuantity] ELSE 0 END) AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 11 THEN [NGQuantity] ELSE 0 END) AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 12 THEN [NGQuantity] ELSE 0 END) AS [Dec-" + this.year.Substring(2, 2) + "],"
                + " SUM([NGQuantity]) AS [" + this.year + "]"
                + " FROM [231_F4Troubles]"
                + " WHERE (DATEPART(year, [Date]) =  " + this.year + " ) AND ([Type] = 0)"
                + " UNION ALL"
                + " SELECT 'Production Total (pcs)' AS [Items],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 1 THEN t2.Quantity ELSE 0 END) AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 2 THEN t2.Quantity ELSE 0 END) AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 3 THEN t2.Quantity ELSE 0 END) AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 4 THEN t2.Quantity ELSE 0 END) AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 5 THEN t2.Quantity ELSE 0 END) AS [May-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 6 THEN t2.Quantity ELSE 0 END) AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 7 THEN t2.Quantity ELSE 0 END) AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 8 THEN t2.Quantity ELSE 0 END) AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 9 THEN t2.Quantity ELSE 0 END) AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 10 THEN t2.Quantity ELSE 0 END) AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 11 THEN t2.Quantity ELSE 0 END) AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 12 THEN t2.Quantity ELSE 0 END) AS [Dec-" + this.year.Substring(2, 2) + "],"
                + " SUM(t2.Quantity) AS [" + this.year + "]"
                + " FROM [241_ExportPlan] AS t1 INNER JOIN"
                + " [231_TimePlan] AS t2 ON (t1.PlanNo=t2.PlanNo)  AND (t1.RobotNo=t2.CompID)"
                + " WHERE SUBSTRING(CONVERT(NVARCHAR(8), t2.[FinishedDate], 112), 1, 4) = '" + this.year + "'";            
            
            totalDataSet = mySQLManagement.SQL_Query(sql);
            if (totalDataSet != null)
            {                
                try
                {
                    this.TotalTable = totalDataSet.Tables[0].Copy();
                    
                    for (int i = 1; i < this.TotalTable.Columns.Count; i++)
                    {
                        double pro_qty = 0;
                        double ng_qty = 0;
                        Double.TryParse(this.TotalTable.Rows[2][i].ToString(), out pro_qty);
                        Double.TryParse(this.TotalTable.Rows[1][i].ToString(), out ng_qty);
                        if (pro_qty > 0)
                        {
                            TotalTable.Rows[0][i] = ng_qty/pro_qty;
                        }
                        if (i < this.TotalTable.Columns.Count - 1)
                        {
                            DataRow ratio_row = RatioTable.NewRow();
                            ratio_row["Month"] = this.TotalTable.Columns[i].ColumnName;
                            ratio_row["Ratio"] = Math.Round(Double.Parse(this.TotalTable.Rows[0][i].ToString()), 4);
                            RatioTable.Rows.Add(ratio_row);
                        }

                        this.TotalTable.Rows[0][i] = Math.Round(Double.Parse(this.TotalTable.Rows[0][i].ToString())*100, 2);
                    }
                    
                    this.ShowTable = this.TotalTable.Clone();
                    for (int i = 0; i < ShowTable.Columns.Count; i++)
                    {
                        ShowTable.Columns[i].DataType = typeof(string);
                    }

                    foreach (DataRow total_row in this.TotalTable.Rows)
                    {
                        ShowTable.ImportRow(total_row);
                    }
                    
                    for (int i = 1; i < this.ShowTable.Columns.Count; i++)
                    {
                        this.ShowTable.Rows[0][i] = this.ShowTable.Rows[0][i].ToString() + "%";                        
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else
            {
                MessageBox.Show("Cannot Load data to report.");
            }

            // Get data for Local Trouble Report by Mistake
            string sql_mistake = "  SELECT t1.MistakeName, t1.MistakeCode,"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 1 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.NGCode = t1.MistakeCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 2 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.NGCode = t1.MistakeCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 3 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.NGCode = t1.MistakeCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 4 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.NGCode = t1.MistakeCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 5 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.NGCode = t1.MistakeCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [May-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 6 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.NGCode = t1.MistakeCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 7 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.NGCode = t1.MistakeCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 8 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.NGCode = t1.MistakeCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 9 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.NGCode = t1.MistakeCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 10 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.NGCode = t1.MistakeCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 11 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.NGCode = t1.MistakeCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 12 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.NGCode = t1.MistakeCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Dec-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.NGCode = t1.MistakeCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [" + this.year + "]"
                + " FROM [231_Mistake] AS t1 LEFT OUTER JOIN"
                + " [231_F4Troubles] AS t2 ON t2.NGCode = t1.MistakeCode"
                + " GROUP BY t1.MistakeCode, t1.MistakeName"
                + " ORDER BY CONVERT(INT, SUBSTRING(t1.MistakeCode, 4, LEN(t1.MistakeCode) - 3))";
            
            mistakeDataSet = mySQLManagement.SQL_Query(sql_mistake);
            if (mistakeDataSet != null)
            {
                MistakeTable = mistakeDataSet.Tables[0];

                this.MistakeTableShow = this.MistakeTable.Clone();
                for (int i = 0; i < MistakeTableShow.Columns.Count; i++)
                {
                    MistakeTableShow.Columns[i].DataType = typeof(string);
                }

                foreach (DataRow mistake_row in this.MistakeTable.Rows)
                {
                    MistakeTableShow.ImportRow(mistake_row);
                }

                int total = 0;                
                DataRow mistake_total = MistakeTableShow.NewRow();
                mistake_total[1] = "Total";                
                for (int i = 2; i < MistakeTable.Columns.Count; i++)
                {
                    total = 0;
                    for (int j = 0; j < MistakeTable.Rows.Count; j++)
                    {
                        total += Int32.Parse(MistakeTable.Rows[j][i].ToString());
                    }
                    mistake_total[i] = total;                    
                }
                MistakeTableShow.Rows.Add(mistake_total);
                
                MistakeTableShow.Columns.Add("%", typeof(string));
                for (int i = 0; i < MistakeTable.Rows.Count; i++)
                {                    
                    MistakeTableShow.Rows[i]["%"] = Math.Round((Double.Parse(MistakeTable.Rows[i][this.year].ToString()) / total) * 100, 2).ToString() + "%";

                    DataRow mistake_rate = MistakeRateChartTable.NewRow();
                    mistake_rate["Items"] = MistakeTable.Rows[i][0].ToString();
                    mistake_rate["Value"] = Math.Round((Double.Parse(MistakeTable.Rows[i][this.year].ToString()) / total), 4);
                    MistakeRateChartTable.Rows.Add(mistake_rate);
                }

                MistakeTableShow.Rows[MistakeTableShow.Rows.Count - 1][MistakeTableShow.Columns.Count - 1] = "100%";                

                MistakeChartTable.Columns.Add("Month", typeof(string));
                for (int i = 0; i < MistakeTable.Rows.Count; i++)
                {
                    MistakeChartTable.Columns.Add(MistakeTable.Rows[i][0].ToString(), typeof(int));
                }

                for (int i = 2; i < MistakeTable.Columns.Count - 1; i++)
                {
                    DataRow mistake_chart_row = this.MistakeChartTable.NewRow();
                    mistake_chart_row["Month"] = MistakeTable.Columns[i].ColumnName;
                    this.MistakeChartTable.Rows.Add(mistake_chart_row);
                }

                for (int i = 0; i < MistakeTable.Rows.Count; i++)
                {
                    for (int j = 2; j < MistakeTable.Columns.Count - 1; j++)
                    {
                        MistakeChartTable.Rows[j - 2][MistakeTable.Rows[i][0].ToString()] = Int32.Parse(MistakeTable.Rows[i][j].ToString());
                    }
                }
            }

            // Get data for Local Trouble Report by Reason
            string sql_reason = "SELECT t1.ReasonName, t1.ReasonCode,"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 1 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.ReasonCode = t1.ReasonCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 2 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.ReasonCode = t1.ReasonCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 3 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.ReasonCode = t1.ReasonCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 4 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.ReasonCode = t1.ReasonCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 5 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.ReasonCode = t1.ReasonCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [May-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 6 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.ReasonCode = t1.ReasonCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 7 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.ReasonCode = t1.ReasonCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 8 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.ReasonCode = t1.ReasonCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 9 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.ReasonCode = t1.ReasonCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 10 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.ReasonCode = t1.ReasonCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 11 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.ReasonCode = t1.ReasonCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[Date]) = 12 AND DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.ReasonCode = t1.ReasonCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [Dec-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(year, t2.[Date]) = " + this.year + " AND (t2.ReasonCode = t1.ReasonCode) AND (t2.[Type] = 0) THEN 1 ELSE 0 END) AS [" + this.year + "]"
                + " FROM [231_MistakeReason] AS t1 LEFT OUTER JOIN"
                + " [231_F4Troubles] AS t2 ON t2.ReasonCode = t1.ReasonCode"
                + " GROUP BY t1.ReasonCode, t1.ReasonName"
                + " ORDER BY CONVERT(INT, SUBSTRING(t1.ReasonCode, 4, LEN(t1.ReasonCode) - 3))";

            reasonDataSet = mySQLManagement.SQL_Query(sql_reason);
            if (reasonDataSet != null)
            {
                ReasonTable = reasonDataSet.Tables[0];

                this.ReasonTableShow = this.ReasonTable.Clone();
                for (int i = 0; i < ReasonTableShow.Columns.Count; i++)
                {
                    ReasonTableShow.Columns[i].DataType = typeof(string);
                }

                foreach (DataRow reason_row in this.ReasonTable.Rows)
                {
                    ReasonTableShow.ImportRow(reason_row);
                }

                int total2 = 0;                
                DataRow reason_total = ReasonTableShow.NewRow();
                reason_total[1] = "Total";
                for (int i = 2; i < ReasonTable.Columns.Count; i++)
                {
                    total2 = 0;
                    for (int j = 0; j < ReasonTable.Rows.Count; j++)
                    {
                        total2 += Int32.Parse(ReasonTable.Rows[j][i].ToString());
                    }
                    reason_total[i] = total2;                    
                }
                ReasonTableShow.Rows.Add(reason_total);

                ReasonTableShow.Columns.Add("%", typeof(string));
                for (int i = 0; i < ReasonTable.Rows.Count; i++)
                {
                    ReasonTableShow.Rows[i]["%"] = Math.Round((Double.Parse(ReasonTable.Rows[i][this.year].ToString()) / total2) * 100, 2).ToString() + "%";

                    DataRow reason_rate = ReasonRateChartTable.NewRow();
                    reason_rate["Items"] = ReasonTable.Rows[i][0].ToString();
                    reason_rate["Value"] = Math.Round((Double.Parse(ReasonTable.Rows[i][this.year].ToString()) / total2), 4);
                    ReasonRateChartTable.Rows.Add(reason_rate);
                }

                ReasonTableShow.Rows[ReasonTableShow.Rows.Count - 1][ReasonTableShow.Columns.Count - 1] = "100%";

                ReasonChartTable.Columns.Add("Month", typeof(string));
                for (int i = 0; i < ReasonTable.Rows.Count; i++)
                {
                    ReasonChartTable.Columns.Add(ReasonTable.Rows[i][0].ToString(), typeof(int));
                }

                for (int i = 2; i < ReasonTable.Columns.Count - 1; i++)
                {
                    DataRow reason_chart_row = this.ReasonChartTable.NewRow();
                    reason_chart_row["Month"] = ReasonTable.Columns[i].ColumnName;
                    this.ReasonChartTable.Rows.Add(reason_chart_row);
                }

                for (int i = 0; i < ReasonTable.Rows.Count; i++)
                {
                    for (int j = 2; j < ReasonTable.Columns.Count - 1; j++)
                    {
                        ReasonChartTable.Rows[j - 2][ReasonTable.Rows[i][0].ToString()] = Int32.Parse(ReasonTable.Rows[i][j].ToString());
                    }
                }
            }

            // Get data for Local Trouble Report by NG Type
            string sql_ngtype = "SELECT TroubleKind,"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 1 THEN NGQuantity ELSE 0 END) AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 2 THEN NGQuantity ELSE 0 END) AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 3 THEN NGQuantity ELSE 0 END) AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 4 THEN NGQuantity ELSE 0 END) AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 5 THEN NGQuantity ELSE 0 END) AS [May-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 6 THEN NGQuantity ELSE 0 END) AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 7 THEN NGQuantity ELSE 0 END) AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 8 THEN NGQuantity ELSE 0 END) AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 9 THEN NGQuantity ELSE 0 END) AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 10 THEN NGQuantity ELSE 0 END) AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 11 THEN NGQuantity ELSE 0 END) AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 12 THEN NGQuantity ELSE 0 END) AS [Dec-" + this.year.Substring(2, 2) + "],"
                + " SUM(NGQuantity) AS [" + this.year + "]"
                + " FROM [231_F4Troubles]"
                + " WHERE (DATEPART(year, [Date]) = " + this.year + ")  AND ([Type] = 0)"
                + " GROUP BY TroubleKind";

            ngtypeDataSet = mySQLManagement.SQL_Query(sql_ngtype);
            if (reasonDataSet != null)
            {
                NGTypeTable = ngtypeDataSet.Tables[0];

                this.NGTypeTableShow = this.NGTypeTable.Clone();
                for (int i = 0; i < NGTypeTableShow.Columns.Count; i++)
                {
                    NGTypeTableShow.Columns[i].DataType = typeof(string);
                }

                foreach (DataRow ngtype_row in this.NGTypeTable.Rows)
                {
                    NGTypeTableShow.ImportRow(ngtype_row);
                }

                int total3 = 0;
                DataRow ngtype_total = NGTypeTableShow.NewRow();
                ngtype_total[1] = "Total";
                for (int i = 2; i < NGTypeTable.Columns.Count; i++)
                {
                    total3 = 0;
                    for (int j = 0; j < NGTypeTable.Rows.Count; j++)
                    {
                        total3 += Int32.Parse(NGTypeTable.Rows[j][i].ToString());
                    }
                    ngtype_total[i] = total3;
                }
                NGTypeTableShow.Rows.Add(ngtype_total);

                NGTypeTableShow.Columns.Add("%", typeof(string));
                for (int i = 0; i < NGTypeTable.Rows.Count; i++)
                {
                    NGTypeTableShow.Rows[i]["%"] = Math.Round((Double.Parse(NGTypeTable.Rows[i][this.year].ToString()) / total3) * 100, 2).ToString() + "%";

                    DataRow ngtype_rate = NGTypeRateChartTable.NewRow();
                    ngtype_rate["Items"] = NGTypeTable.Rows[i][0].ToString();
                    ngtype_rate["Value"] = Math.Round((Double.Parse(NGTypeTable.Rows[i][this.year].ToString()) / total3), 4);
                    NGTypeRateChartTable.Rows.Add(ngtype_rate);
                }

                NGTypeTableShow.Rows[NGTypeTableShow.Rows.Count - 1][NGTypeTableShow.Columns.Count - 1] = "100%";                
            }

            // Get data for Local Trouble Target Report
            string sql_target = "SELECT 'PLAN' AS [Items],"
                + " " + this.plan + " AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [May-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Dec-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [" + this.year + "]"
                + " UNION ALL"
                + " SELECT 'ACTUAL' AS [Items],"
                + " 0 AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [May-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Dec-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [" + this.year + "]"
                + " UNION ALL"
                + " SELECT 'NG (PC)' AS [Items],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 1 THEN NGQuantity ELSE 0 END) AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 2 THEN NGQuantity ELSE 0 END) AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 3 THEN NGQuantity ELSE 0 END) AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 4 THEN NGQuantity ELSE 0 END) AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 5 THEN NGQuantity ELSE 0 END) AS [May-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 6 THEN NGQuantity ELSE 0 END) AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 7 THEN NGQuantity ELSE 0 END) AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 8 THEN NGQuantity ELSE 0 END) AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 9 THEN NGQuantity ELSE 0 END) AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 10 THEN NGQuantity ELSE 0 END) AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 11 THEN NGQuantity ELSE 0 END) AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 12 THEN NGQuantity ELSE 0 END) AS [Dec-" + this.year.Substring(2, 2) + "],"
                + " SUM(NGQuantity) AS [" + this.year + "]"
                + " FROM [231_F4Troubles]"
                + " WHERE (DATEPART(year, [Date]) = " + this.year + ")  AND ([Type] = 0)"
                + " UNION ALL"
                + " SELECT 'QTY COMP' AS [Items],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 1 THEN t2.Quantity ELSE 0 END) AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 2 THEN t2.Quantity ELSE 0 END) AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 3 THEN t2.Quantity ELSE 0 END) AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 4 THEN t2.Quantity ELSE 0 END) AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 5 THEN t2.Quantity ELSE 0 END) AS [May-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 6 THEN t2.Quantity ELSE 0 END) AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 7 THEN t2.Quantity ELSE 0 END) AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 8 THEN t2.Quantity ELSE 0 END) AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 9 THEN t2.Quantity ELSE 0 END) AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 10 THEN t2.Quantity ELSE 0 END) AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 11 THEN t2.Quantity ELSE 0 END) AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.[FinishedDate]) = 12 THEN t2.Quantity ELSE 0 END) AS [Dec-" + this.year.Substring(2, 2) + "],"
                + " SUM(t2.Quantity) AS [" + this.year + "]"
                + " FROM [241_ExportPlan] AS t1 INNER JOIN"
                + " [231_TimePlan] AS t2 ON (t1.PlanNo=t2.PlanNo)  AND (t1.Robotno=t2.CompID)"
                + " WHERE SUBSTRING(CONVERT(NVARCHAR(8), t2.[FinishedDate], 112), 1, 4) = '" + this.year + "'";

            targetDataSet = mySQLManagement.SQL_Query(sql_target);
            if (targetDataSet != null)
            {
                TargetTable = targetDataSet.Tables[0];

                for (int i = 1; i < this.TargetTable.Columns.Count; i++)
                {
                    double act = Double.Parse(this.TargetTable.Rows[2][i].ToString());
                    double qty = Double.Parse(this.TargetTable.Rows[3][i].ToString());
                    if (qty > 0)
                    {
                        this.TargetTable.Rows[1][i] = Math.Round((act / qty) * 100, 2);
                    }
                }

                this.TargetTableShow = this.TargetTable.Clone();
                for (int i = 0; i < TargetTableShow.Columns.Count; i++)
                {
                    TargetTableShow.Columns[i].DataType = typeof(string);
                }

                foreach (DataRow target_row in this.TargetTable.Rows)
                {
                    TargetTableShow.ImportRow(target_row);
                }

                for (int i = 1; i < TargetTableShow.Columns.Count; i++)
                {
                    TargetTableShow.Rows[0][i] = TargetTableShow.Rows[0][i].ToString() + "%";
                    TargetTableShow.Rows[1][i] = TargetTableShow.Rows[1][i].ToString() + "%";
                }

                TargetChartTable.Columns.Add("Month", typeof(string));
                TargetChartTable.Columns.Add("Plan", typeof(double));
                TargetChartTable.Columns.Add("Actual", typeof(double));
                TargetChartTable.Columns.Add("NGQty", typeof(double));
                TargetChartTable.Columns.Add("TotalQty", typeof(double));
                                
                for (int i = 1; i < TargetTable.Columns.Count - 1; i++)
                {
                    DataRow target_char_row = TargetChartTable.NewRow();
                    target_char_row["Month"] = TargetTable.Columns[i].ColumnName;
                    target_char_row["Plan"] = Double.Parse(TargetTable.Rows[0][i].ToString()) / 100;
                    target_char_row["Actual"] = Double.Parse(TargetTable.Rows[1][i].ToString()) / 100;
                    target_char_row["NGQty"] = Double.Parse(TargetTable.Rows[2][i].ToString());
                    target_char_row["TotalQty"] = Double.Parse(TargetTable.Rows[3][i].ToString());
                    TargetChartTable.Rows.Add(target_char_row);                    
                }
            }

            BindData();
        }
    }
}
